1.rollup的作用

用来对每个分组的数据进行小计或者合计。

2.求每个学生的所有课程成绩的平均分

1)使用pl/sql查询SC表，这个表是安装oracle时默认安装的scott用户下的表。

2)查询每个学生的平均成绩

3.统计所有学生的平均成绩

那我们如何将每个学生的平均成绩和所有学生的平均成绩放在一个查询结果里呢？这就需要使用到rollup函数。

4.rollup函数的使用

1)使用rollup

select t.sno, avg(t.score) from SC t group by rollup(t.sno);

2)改进rollup

上一个查询结果第5行的SNO字段是空的，需要我们填入默认值。这里可以使用nvl函数。

select nvl(t.sno,'所有学生'), avg(t.score) from SC t group by rollup(t.sno);

5.学生平均成绩排名，rank函数的使用

select t.sno, avg(t.score) "平均分", rank() over(order by avg(t.score) desc) "成绩排名" from SC t group by t.sno;

rank()是oracle排序函数，over(order by avg(t.score) desc)是开窗函数。表示按照avg(t.score)值倒序排列。

如果我想把所有学生的平均成绩放进这个查询结果里可以吗？我们试试。

6.查询每个学生的平均成绩和所有学生的平均成绩，并且按照成绩倒序排列

1)rollup和rank函数结合使用

select nvl(t.sno,'所有学生'), avg(t.score) "平均分", rank() over(order by avg(t.score) desc) "成绩排名" from SC t group by rollup(t.sno);

我想把统计结果排除在排序函数外怎么办？也就是我只想让每个学生按照平均成绩排序，所有学生的平均成绩不计入排序函数。

2)去掉统计结果行的排序

select nvl(t.sno, '所有学生'),

avg(t.score) "平均分",

rank() over(Partition by grouping(t.sno) order by avg(t.score) desc) "成绩排名"

from SC t

group by rollup(t.sno);

在over()开窗函数中加入Partition by grouping(t.sno)，表示按照grouping(t.sno)进行分组。

grouping函数解释： 如果显示“1”表示GROUPING函数对应的列(例如sno字段)是由于ROLLUP函数所产生的空值对应的信息，即对此列进行汇总计算后的结果。

如果显示“0”表示此行对应的这列参未与ROLLUP函数分组汇总活动。

我们这样理解：使用rollup函数后，在统计列(如sno字段)上使用grouping函数后，合计行和非合计行的返回值不一样。我们就可以使用grouping(t.sno)进行分组。

3)再次改进，将‘所有学生’行的成绩排名字段置空

select nvl(t.sno, '所有学生'),

avg(t.score) "平均分",

case

when grouping(t.sno) = 1 then

null

else

rank()

over(Partition by grouping(t.sno) order by avg(t.score) desc)

end "成绩排名"

from SC t

group by rollup(t.sno);

这里就是case when和grouping(t.sno)结合使用。